package dao;

import model.Comment;
import model.Page;
import model.Reply;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import utils.ArrayBean;
import utils.C3P0Util;
import utils.MyBeanProcessor;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 评论的数据库底层操作
 */
public class CommentDAO {

    /**
     * 一个数据集的handler，处理完的数据将是list<objectp[]>
     */
    class CommentHandler implements ResultSetHandler {

        private Map<Integer, String> meta = null;

        public Map<Integer, String> getMeta() {
            return meta;
        }

        /**
         * 保存列标题
         *
         * @param rs 结果集
         * @throws SQLException
         */
        private void setColumnData(ResultSet rs) throws SQLException {
            meta = new HashMap<Integer, String>();
            ResultSetMetaData rsmd = rs.getMetaData();
            int count = rsmd.getColumnCount();
            for (int i = 1; i <= count; i++) {
                meta.put(i, rsmd.getColumnName(i));
            }
        }

        @Override
        public Object handle(ResultSet rs) throws SQLException {
            List<Object[]> rows = new ArrayList<Object[]>();
            setColumnData(rs);

            while (rs.next()) {
                rows.add(this.handleRow(rs));
            }
            return rows;

        }

        /**
         * 处理结果集的每一行
         *
         * @param rs 结果集
         * @return 数组
         * @throws SQLException
         */
        public Object[] handleRow(ResultSet rs) throws SQLException {
            ResultSetMetaData meta = rs.getMetaData();
            int cols = meta.getColumnCount();
            Object[] result = new Object[cols];

            for (int i = 0; i < cols; i++) {
                result[i] = rs.getObject(i + 1);
            }

            return result;
        }
    }

    public void testdb(int comic_id) {

        Connection coon = C3P0Util.getConnection();
        String sql = "select * from comment A,reply B where A.comic_id=? and B.comment_id=A.id";
        PreparedStatement ps = null;
        ResultSet rs = null;

        Comment comment = null;
        Reply reply = null;
        List<Comment> comments = new ArrayList<Comment>();
        List<Reply> replies = new ArrayList<Reply>();
        MyBeanProcessor mbp = new MyBeanProcessor();

        try {
            if (coon != null) {
                ps = coon.prepareStatement(sql);
                ps.setInt(1, comic_id);
                rs = ps.executeQuery();
                mbp.toBean2(rs, Comment.class);
//                while (rs.next()) {
//                    //对每一行的处理
//                    ResultSetMetaData rsmd = rs.getMetaData();
//                    int cols = rsmd.getColumnCount();
//                    for (int i = 1; i <= cols; i++) {
//                        System.out.println("getColumnName: " + rsmd.getColumnName(i) + ",  getObject: " + rs.getObject(i));
//                    }
//                }
            }

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }

    public void testdb2(int comic_id) throws IllegalAccessException, SQLException, InstantiationException {
        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
        Object[] params = {comic_id};
        String sql = "select * from comment A,reply B where A.comic_id=? and B.comment_id=A.id";
        CommentHandler ch = new CommentHandler();
        List<Object[]> resultlist = null;
        try {
            resultlist = (List<Object[]>) qr.query(sql, ch, params);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        List<Comment> commentlist = new ArrayList<Comment>();//要返回的评论数组
        Map<Integer, String> meta = ch.getMeta();//列名信息

        ArrayBean commentBean = new ArrayBean();
        ArrayBean replyBean = new ArrayBean();
        commentBean.setDefault(Comment.class, 1, 5, meta);
        replyBean.setDefault(Reply.class, 6, 5, meta);

        Comment lastComment = null;
        if (resultlist != null) {
            for (Object[] each : resultlist) {
                Comment comment = (Comment) commentBean.toBean(each);
                Reply reply = (Reply) replyBean.toBean(each);
                if (lastComment == null || (comment.getId() != lastComment.getId())) {
                    comment.setReplys(new ArrayList<Reply>());
                    comment.getReplys().add(reply);
                    commentlist.add(comment);
                    lastComment = comment;
                } else {
                    lastComment.getReplys().add(reply);
                }
            }
        }
        System.out.println(ch.getMeta());
    }

    /**
     * 根据漫画id获得该漫画的所有评论
     *
     * @param comicId 漫画id
     * @return 评论的list
     */
    public List<Comment> getComicComments(int comicId, Page page) {
        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
        Object[] params = {comicId, page.getStart(), page.getPageSize()};

        String sql = "select * from comment where comic_id=? order by time desc limit ?,?";

        List<Comment> lists_comment = null;
        try {
            lists_comment = (List<Comment>) qr.query(sql, new BeanListHandler(Comment.class), params);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return lists_comment;
    }

    /**
     * 获得该漫画的评论数量
     *
     * @param comicId 漫画id
     * @return 数量
     */
    public int getCommentCounts(int comicId) {
        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
        Object[] params = {comicId};

        String sql = "select count(*) from comment where comic_id=?";
        Long count = 0L;
        try {
            count = (Long) qr.query(sql, new ScalarHandler(), params);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return count.intValue();
    }

    /**
     * 添加评论
     *
     * @param user_name 用户名
     * @param comic_id  漫画id
     * @param content   内容
     * @return true如果成功更新
     */
    public boolean addComment(String user_name, int comic_id, String content) {
        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());

        Timestamp timestamp = new Timestamp(System.currentTimeMillis());

        Object[] params = {user_name, comic_id, content, timestamp};

        String sql = "INSERT INTO comment (id, user_name, comic_id, content, time) VALUES (NULL, ?, ?, ?, ?);";
        int row = 0;
        try {
            row = qr.update(sql, params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return row > 0;
    }
}
